package com.itlaobing.demo.util;

import java.io.Closeable;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 数据库操作工具类
 *    1. 获取连接
 *    2. 执行增删改查（修改 / 查询）操作
 *    3. 关闭连接
 * @author Administrator
 *
 */
public class DBHelper implements Closeable{


	static {
		InputStream in = DBHelper.class.getResourceAsStream("/db.properties");
		Properties prop = new Properties();
		try {
			prop.load(in);
			Class.forName(prop.getProperty("driver"));
		} catch (IOException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.err.println("数据库驱动加载失败");
		}
		url = prop.getProperty("url");
		user = prop.getProperty("user");
		pass = prop.getProperty("pass");
	}




	private static String url;

	private static String user;

	private static String pass;

	private Connection conn;

	private PreparedStatement ps;

	// 每一个线程独立
	private static ThreadLocal<Connection> local = new ThreadLocal<>();

	public DBHelper() {}


	/**
	 *  获取连接
	 * @return
	 */
	public Connection getConnection() {
		try {
			if(local.get() == null){
				conn = DriverManager.getConnection(url, user, pass);
				local.set(conn);
			}else{
				conn = local.get();
			}
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 *  执行修改操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeUpdate(String sql, Object...params) {
		// 获取连接
		getConnection();
		// 执行修改操作
		return executeUpdate(conn, sql, params);
	}

	/**
	 *    执行修改操作
	 * @param conn 数据库连接
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeUpdate(Connection conn, String sql, Object...params) {
		try {
			// 获取 prepareStatement 对象
			ps = conn.prepareStatement(sql);
			// 给 sql 赋值
			setParams(params);
			// 执行 sql
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 *  执行 insert 操作，返回主键
	 */
	public Object executeInsert(String sql, Object...params) {
		getConnection();
		return executeInsert(conn, sql, params);
	}

	public Object executeInsert(Connection conn, String sql, Object...params) {
		try {
			// 获取 prepareStatement 对象
			ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			// 给 sql 赋值
			setParams(params);
			// 执行 sql
			int rows = ps.executeUpdate();
			if (rows > 0) {
				ResultSet set = ps.getGeneratedKeys();
				while(set.next()) {
					return set.getObject(1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ResultSet executeQuery(String sql, Object...params) {
		getConnection();
		try {
			ps = conn.prepareStatement(sql);
			setParams(params);
			return ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	public <T> T executeQuery(String sql, Class<T> c, Object...params) {
		ResultSet set = executeQuery(sql, params);
		try {
			if(set.next()){
				return reverce(c, set);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public <T> List<T> executeQueryList(String sql, Class<T> c, Object...params){
		List<T> list = new ArrayList<>();
		ResultSet set = executeQuery(sql, params);
		try {
			while(set.next()) {
				list.add(reverce(c, set));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 关闭连接
	 */
	@Override
	public void close() {
		local.remove();
		try {
			if (ps != null) {
				ps.close();
			}
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	private void setParams(Object...params) throws SQLException {
		// 给 sql 赋值
		for (int i = 0; i < params.length; i++) {
			ps.setObject(i + 1, params[i]);
		}
	}

	private <T> T reverce(Class<T> c, ResultSet set) {
		/**
		 *  TODO(如果实体中有继承关系)
		 *  TODO(如果没有无参构造)
		 */

		// 结果集的列名 = t 的 field 名
		try {
			// 结果集 元数据
			ResultSetMetaData meta = set.getMetaData();
			// 创建一个对象
			T t = c.newInstance();
			// 获取到对象中声明的属性
			Field[] fields = c.getDeclaredFields();

			for (int i = 0; i < meta.getColumnCount(); i++) {
				// 列名
				String label = meta.getColumnLabel(i + 1);
				for (int j = 0; j < fields.length; j++) {
					// 列名和属性名一样时
					if (label.equals(fields[j].getName())) {
						// 将属性设置为可访问的
						fields[j].setAccessible(true);
						// 设置值
						fields[j].set(t, set.getObject(i + 1));
						break;
					}
				}
			}
			return t;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void begin(){
		getConnection();
		try {
			conn.setAutoCommit(false);
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
	}

	public void commit(){
		try {
			conn.commit();
			local.remove();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
	}

	public void rollback(){
		try {
			conn.rollback();
			local.remove();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
	}

	public static void main(String[] args) {
		System.out.println(new DBHelper().getConnection());
	}

}
